{
 "nbformat": 4,
 "nbformat_minor": 2,
 "metadata": {
  "language_info": {
   "name": "python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "version": "3.7.6-final"
  },
  "orig_nbformat": 2,
  "file_extension": ".py",
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3,
  "kernelspec": {
   "name": "python37664bitwuhan2019ncovpipenv5f20d42ee8464e6b95cd78c3cee9d475",
   "display_name": "Python 3.7.6 64-bit ('Wuhan-2019-nCoV': pipenv)"
  }
 },
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>date</th>\n      <th>country</th>\n      <th>countryCode</th>\n      <th>province</th>\n      <th>provinceCode</th>\n      <th>city</th>\n      <th>cityCode</th>\n      <th>confirmed</th>\n      <th>suspected</th>\n      <th>cured</th>\n      <th>dead</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>2020-01-13</td>\n      <td>中国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>41</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2020-01-14</td>\n      <td>中国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>41</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2020-01-15</td>\n      <td>中国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>41</td>\n      <td>0</td>\n      <td>5</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2020-01-16</td>\n      <td>中国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>45</td>\n      <td>0</td>\n      <td>8</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2020-01-17</td>\n      <td>中国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>62</td>\n      <td>0</td>\n      <td>12</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>220</th>\n      <td>2020-01-27</td>\n      <td>澳大利亚</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>216</th>\n      <td>2020-01-27</td>\n      <td>美国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>218</th>\n      <td>2020-01-27</td>\n      <td>越南</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>215</th>\n      <td>2020-01-27</td>\n      <td>韩国</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>4</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>224</th>\n      <td>2020-01-27</td>\n      <td>马来西亚</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td>4</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>334 rows × 11 columns</p>\n</div>",
      "text/plain": "           date country countryCode province provinceCode city cityCode  \\\n1    2020-01-13      中国                                                   \n2    2020-01-14      中国                                                   \n3    2020-01-15      中国                                                   \n4    2020-01-16      中国                                                   \n5    2020-01-17      中国                                                   \n..          ...     ...         ...      ...          ...  ...      ...   \n220  2020-01-27    澳大利亚                                                   \n216  2020-01-27      美国                                                   \n218  2020-01-27      越南                                                   \n215  2020-01-27      韩国                                                   \n224  2020-01-27    马来西亚                                                   \n\n     confirmed  suspected  cured  dead  \n1           41          0      0     1  \n2           41          0      0     1  \n3           41          0      5     2  \n4           45          0      8     2  \n5           62          0     12     2  \n..         ...        ...    ...   ...  \n220          5          0      0     0  \n216          5          0      0     0  \n218          2          0      0     0  \n215          4          0      0     0  \n224          4          0      0     0  \n\n[334 rows x 11 columns]"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 更新数据\n",
    "import re\n",
    "import pandas as pd\n",
    "\n",
    "csv_file = \"Wuhan-2019-nCoV.csv\"\n",
    "json_file = \"Wuhan-2019-nCoV.json\"\n",
    "\n",
    "df = pd.read_csv(csv_file)\n",
    "df[\"date\"] = df[\"date\"].map(lambda x: \"-\".join([i.zfill(2) for i in re.split(\"\\\\D+\", x)]))\n",
    "df = pd.concat([df, cn_area_df, cn_global_df, cn_day_df], sort=False)\n",
    "df[\"country\"].fillna(\"\", inplace=True)\n",
    "df[\"countryCode\"].fillna(\"\", inplace=True)\n",
    "df[\"province\"].fillna(\"\", inplace=True)\n",
    "df[\"provinceCode\"].fillna(\"\", inplace=True)\n",
    "df[\"city\"].fillna(\"\", inplace=True)\n",
    "df[\"cityCode\"].fillna(\"\", inplace=True)\n",
    "df[\"confirmed\"] = df[\"confirmed\"].fillna(0).astype(int)\n",
    "df[\"suspected\"] = df[\"suspected\"].fillna(0).astype(int)\n",
    "df[\"cured\"] = df[\"cured\"].fillna(0).astype(int)\n",
    "df[\"dead\"] = df[\"dead\"].fillna(0).astype(int)\n",
    "df.drop_duplicates(subset=[\"date\", \"country\", \"province\", \"city\"], keep=\"last\", inplace=True)\n",
    "df.sort_values([\"date\", \"country\", \"province\", \"city\"], na_position=\"first\", inplace=True)\n",
    "df.to_csv(csv_file, index=False, encoding='utf-8')\n",
    "df.to_json(json_file, orient=\"records\", force_ascii=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import math\n",
    "from functools import lru_cache\n",
    "import pandas as pd\n",
    "\n",
    "csv_file = \"Wuhan-2019-nCoV.csv\"\n",
    "\n",
    "country_code = pd.read_csv(\"CountryCode.csv\")\n",
    "china_area_code = pd.read_csv(\"ChinaAreaCode.csv\")\n",
    "china_area_code[\"code\"] = china_area_code[\"code\"].astype(str)\n",
    "# china_area_code = china_area_code[china_area_code.apply(lambda x: bool(re.match(\"\\\\d{4}00$\", x.code)), axis=1)]\n",
    "china_area_code[\"is_province\"] = china_area_code[\"code\"].map(lambda x: bool(re.match(\"\\\\d{2}0000$\", x)))\n",
    "# china_area_code[\"city_code\"] = china_area_code[\"code\"].map(lambda x: re.sub(\"\\\\d{2}$\", \"00\", x))\n",
    "china_area_code[\"province_code\"] = china_area_code[\"code\"].map(lambda x: re.sub(\"\\\\d{4}$\", \"0000\", x))\n",
    "\n",
    "@lru_cache(maxsize = 128)\n",
    "def get_country_code(name):\n",
    "    result =  country_code.loc[country_code[\"name\"].isin([name])][\"code\"]\n",
    "    if (len(result.values) > 0):\n",
    "        return result.values[0]\n",
    "    return \"\"\n",
    "\n",
    "\n",
    "@lru_cache(maxsize = 32)\n",
    "def get_china_province_code(name):\n",
    "    if not name:\n",
    "        return \"\"\n",
    "    result = china_area_code.loc[china_area_code[\"is_province\"] & china_area_code[\"name\"].str.contains(name)][\"code\"]\n",
    "    if (len(result.values) > 0):\n",
    "        return result.values[0]\n",
    "    return \"\"\n",
    "\n",
    "\n",
    "# @lru_cache(maxsize = 1024)\n",
    "def get_china_city_code(province_code, name):\n",
    "    if not name or not province_code:\n",
    "        return \"\"\n",
    "    result = china_area_code.loc[china_area_code[\"province_code\"].isin([province_code]) & ~china_area_code[\"is_province\"] & china_area_code[\"name\"].str.contains(name)][\"code\"]\n",
    "    if (len(result.values) > 0):\n",
    "        return result.values[0]\n",
    "\n",
    "    for i in range(1, len(name)):\n",
    "        fuzzy_name = name[:-i] + \".*\" + \".*\".join(name[-i:])\n",
    "        result = china_area_code.loc[china_area_code[\"province_code\"].isin([province_code]) & ~china_area_code[\"is_province\"] & china_area_code[\"name\"].str.match(fuzzy_name)][\"code\"]\n",
    "        if (len(result.values) > 0):\n",
    "            print(f\"\"\"{province_code} {fuzzy_name} -> {\",\".join(result.values)}\"\"\")\n",
    "            return result.values[0]\n",
    "\n",
    "    return \"\"\n",
    "\n",
    "\n",
    "@lru_cache(maxsize = 1024)\n",
    "def get_china_area_name(code, name):\n",
    "    if not code:\n",
    "        return name\n",
    "    result = china_area_code.loc[china_area_code[\"code\"].isin([code])][\"name\"]\n",
    "    if (len(result.values) > 0):\n",
    "        return result.values[0]\n",
    "    return name\n",
    "\n",
    "df = pd.read_csv(csv_file)\n",
    "df[\"country\"].fillna(\"\", inplace=True)\n",
    "df[\"countryCode\"].fillna(\"\", inplace=True)\n",
    "df[\"province\"].fillna(\"\", inplace=True)\n",
    "df[\"provinceCode\"].fillna(\"\", inplace=True)\n",
    "df[\"city\"].fillna(\"\", inplace=True)\n",
    "df[\"cityCode\"].fillna(\"\", inplace=True)\n",
    "df[\"countryCode\"] = df[\"country\"].map(get_country_code)\n",
    "df[\"provinceCode\"] = df[\"province\"].map(get_china_province_code)\n",
    "df[\"province\"] = df.apply(lambda x: get_china_area_name(x.provinceCode, x.province), axis = 1)\n",
    "df[\"cityCode\"] = df.apply(lambda x: get_china_city_code(x.provinceCode, x.city), axis = 1)\n",
    "df[\"city\"] = df.apply(lambda x: get_china_area_name(x.cityCode, x.city), axis = 1)\n",
    "df.to_csv(\"temp.csv\", index=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "'220200'"
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_china_city_code(\"220000\", \"吉林\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "黔南\n"
    }
   ],
   "source": [
    "import math\n",
    "s = \"黔南州\"\n",
    "n = math.ceil(len(s) / 2)\n",
    "for i in range(1, n):\n",
    "    print(s[:-i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>date</th>\n      <th>countryCode</th>\n      <th>country</th>\n      <th>provinceCode</th>\n      <th>province</th>\n      <th>cityCode</th>\n      <th>city</th>\n      <th>confirmed</th>\n      <th>suspected</th>\n      <th>cured</th>\n      <th>dead</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2020-02-06</td>\n      <td>CN</td>\n      <td>中国</td>\n      <td>420000</td>\n      <td>湖北</td>\n      <td></td>\n      <td></td>\n      <td>19665</td>\n      <td>0</td>\n      <td>677</td>\n      <td>549</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2020-02-06</td>\n      <td>CN</td>\n      <td>中国</td>\n      <td>420000</td>\n      <td>湖北</td>\n      <td>420100</td>\n      <td>武汉</td>\n      <td>10117</td>\n      <td>0</td>\n      <td>413</td>\n      <td>414</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2020-02-06</td>\n      <td>CN</td>\n      <td>中国</td>\n      <td>420000</td>\n      <td>湖北</td>\n      <td>420900</td>\n      <td>孝感</td>\n      <td>1886</td>\n      <td>0</td>\n      <td>9</td>\n      <td>25</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2020-02-06</td>\n      <td>CN</td>\n      <td>中国</td>\n      <td>420000</td>\n      <td>湖北</td>\n      <td>421100</td>\n      <td>黄冈</td>\n      <td>1807</td>\n      <td>0</td>\n      <td>62</td>\n      <td>29</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2020-02-06</td>\n      <td>CN</td>\n      <td>中国</td>\n      <td>420000</td>\n      <td>湖北</td>\n      <td>421300</td>\n      <td>随州</td>\n      <td>834</td>\n      <td>0</td>\n      <td>9</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>1015</th>\n      <td>2020-02-06</td>\n      <td>NaN</td>\n      <td>斯里兰卡</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1016</th>\n      <td>2020-02-06</td>\n      <td>NaN</td>\n      <td>芬兰</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1017</th>\n      <td>2020-02-06</td>\n      <td>NaN</td>\n      <td>瑞典</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1018</th>\n      <td>2020-02-06</td>\n      <td>NaN</td>\n      <td>西班牙</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1019</th>\n      <td>2020-02-06</td>\n      <td>NaN</td>\n      <td>比利时</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>1020 rows × 11 columns</p>\n</div>",
      "text/plain": "            date countryCode country provinceCode province cityCode city  \\\n0     2020-02-06          CN      中国       420000       湖北                 \n1     2020-02-06          CN      中国       420000       湖北   420100   武汉   \n2     2020-02-06          CN      中国       420000       湖北   420900   孝感   \n3     2020-02-06          CN      中国       420000       湖北   421100   黄冈   \n4     2020-02-06          CN      中国       420000       湖北   421300   随州   \n...          ...         ...     ...          ...      ...      ...  ...   \n1015  2020-02-06         NaN    斯里兰卡          NaN      NaN      NaN  NaN   \n1016  2020-02-06         NaN      芬兰          NaN      NaN      NaN  NaN   \n1017  2020-02-06         NaN      瑞典          NaN      NaN      NaN  NaN   \n1018  2020-02-06         NaN     西班牙          NaN      NaN      NaN  NaN   \n1019  2020-02-06         NaN     比利时          NaN      NaN      NaN  NaN   \n\n      confirmed  suspected  cured  dead  \n0         19665          0    677   549  \n1         10117          0    413   414  \n2          1886          0      9    25  \n3          1807          0     62    29  \n4           834          0      9     9  \n...         ...        ...    ...   ...  \n1015          1          0      1     0  \n1016          1          0      0     0  \n1017          1          0      0     0  \n1018          1          0      0     0  \n1019          1          0      0     0  \n\n[1020 rows x 11 columns]"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "import json\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "\n",
    "toutiao_forum = requests.get(\"https://i.snssdk.com/forum/home/v1/info/?forum_id=1656784762444839\").json()\n",
    "toutiao_data = json.loads(toutiao_forum[\"forum\"][\"extra\"][\"ncov_string_list\"])\n",
    "\n",
    "datalist = []\n",
    "\n",
    "dataDate = datetime.fromtimestamp(toutiao_data[\"updateTime\"]).strftime('%Y-%m-%d')\n",
    "for province in toutiao_data[\"provinces\"]:\n",
    "    datalist.append({\n",
    "        \"date\": dataDate,\n",
    "        \"countryCode\": \"CN\",\n",
    "        \"country\": \"中国\",\n",
    "        \"provinceCode\": province[\"id\"].ljust(6, '0'),\n",
    "        \"province\": province[\"name\"],\n",
    "        \"cityCode\": \"\",\n",
    "        \"city\": \"\",\n",
    "        \"confirmed\": province[\"confirmedNum\"],\n",
    "        \"suspected\": 0,\n",
    "        \"cured\": province[\"curesNum\"],\n",
    "        \"dead\": province[\"deathsNum\"]\n",
    "    })\n",
    "    for city in province[\"cities\"]:\n",
    "        datalist.append({\n",
    "            \"date\": dataDate,\n",
    "            \"countryCode\": \"CN\",\n",
    "            \"country\": \"中国\",\n",
    "            \"provinceCode\": province[\"id\"].ljust(6, '0'),\n",
    "            \"province\": province[\"name\"],\n",
    "            \"cityCode\": city[\"id\"].ljust(6, '0'),\n",
    "            \"city\": city[\"name\"],\n",
    "            \"confirmed\": city[\"confirmedNum\"],\n",
    "            \"suspected\": 0,\n",
    "            \"cured\": city[\"curesNum\"],\n",
    "            \"dead\": city[\"deathsNum\"]\n",
    "        })\n",
    "    for province_history in province[\"series\"]:\n",
    "        datalist.append({\n",
    "            \"date\": province_history[\"date\"],\n",
    "            \"countryCode\": \"CN\",\n",
    "            \"country\": \"中国\",\n",
    "            \"provinceCode\": province[\"id\"].ljust(6, '0'),\n",
    "            \"province\": province[\"name\"],\n",
    "            \"cityCode\": \"\",\n",
    "            \"city\": \"\",\n",
    "            \"confirmed\": province_history[\"confirmedNum\"],\n",
    "            \"suspected\": 0,\n",
    "            \"cured\": province_history[\"curesNum\"],\n",
    "            \"dead\": province_history[\"deathsNum\"]\n",
    "        })\n",
    "\n",
    "for cn in toutiao_data[\"nationwide\"]:\n",
    "    datalist.append({\n",
    "        \"date\": cn[\"date\"],\n",
    "        \"countryCode\": \"CN\",\n",
    "        \"country\": \"中国\",\n",
    "        \"provinceCode\": \"\",\n",
    "        \"province\": \"\",\n",
    "        \"cityCode\": \"\",\n",
    "        \"city\": \"\",\n",
    "        \"confirmed\": cn[\"confirmedNum\"],\n",
    "        \"suspected\": cn[\"suspectedNum\"],\n",
    "        \"cured\": cn[\"curesNum\"],\n",
    "        \"dead\": cn[\"deathsNum\"]\n",
    "    })\n",
    "\n",
    "for country in toutiao_data[\"world\"]:\n",
    "    datalist.append({\n",
    "        \"date\": dataDate,\n",
    "        \"country\": country[\"country\"],\n",
    "        \"confirmed\": country[\"confirmedNum\"],\n",
    "        \"suspected\": country[\"suspectedNum\"],\n",
    "        \"cured\": country[\"curesNum\"],\n",
    "        \"dead\": country[\"deathsNum\"]\n",
    "    })\n",
    "\n",
    "\n",
    "df = pd.DataFrame(datalist)\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}